import tushare as ts
import time
import xlwt
import xlrd
import xlutils
import csv
import numpy as np
from pandas import Series, DataFrame
import pandas as pd


#定义查询日期，必须是交易日
s_date = "2017-06-27"
#定义股票代码
#s_code = "000517"
#print('数据来源版本:'+ts.__version__)

#获取当日所有股票基本数据
s_all = ts.get_today_all()
s_all.to_excel('d:/py/test/今日股票.xlsx')
#print(ts.get_today_all())
#获取基本信息
s_basics = ts.get_stock_basics()
#概念分类
s_concept = ts.get_concept_classified()
#行业分类
#s_industry = ts.get_industry_classified()
#地域分类
#s_area = ts.get_area_classified()
#获取机构购买信息(默认五日内)
s_inst = ts.inst_tops()


#导出excel表格
s_basics.to_excel('d:/py/test/today_stock.xlsx')
s_concept.to_excel('d:/py/test/概念数据.xlsx')
#s_industry.to_excel('d:/py/test/行业数据.xlsx')
#s_area.to_excel('d:/py/test/地域数据.xlsx')
#获取当天股票总数
data=xlrd.open_workbook('d:/py/test/today_stock.xlsx')
table=data.sheets()[0] #:表示xls文件的第一个表格，[1]表示第二个表格
nrows = table.nrows 
#print(nrows)
s1=pd.Series(np.array([]))
s2=pd.Series(np.array([]))
s3=pd.Series(np.array([]))
s4=pd.Series(np.array([]))
s5=pd.Series(np.array([]))
s6=pd.Series(np.array([]))
s7=pd.Series(np.array([]))
s8=pd.Series(np.array([]))
s9=pd.Series(np.array([]))
s10=pd.Series(np.array([]))
s11=pd.Series(np.array([]))
s12=pd.Series(np.array([]))
s13=pd.Series(np.array([]))
s14=pd.Series(np.array([]))
s15=pd.Series(np.array([]))
s16=pd.Series(np.array([]))
s17=pd.Series(np.array([]))
s18=pd.Series(np.array([]))
s19=pd.Series(np.array([]))
s20=pd.Series(np.array([]))
s21=pd.Series(np.array([]))
s22=pd.Series(np.array([]))
s23=pd.Series(np.array([]))
s24=pd.Series(np.array([]))
s25=pd.Series(np.array([]))
s26=pd.Series(np.array([]))
s27=pd.Series(np.array([]))
s28=pd.Series(np.array([]))
s_stock=pd.DataFrame({"s1_code":s1,"s2_name":s2,"s3_date":s3,"s4_close":s4,"s5_MA5":s5,"s6_MA10":s6,"s7_MA20":s7,"s8_VMA5":s8,"s9_VMA10":s9,"s10_VMA20":s10,"s11_volume":s11,"s12_turnover":s12,"s13_p_change":s13,"s14_gainian":s14,"s15_area":s15,"s16_industry":s16,"s17_pe":s17,"s18_outstanding":s18,"s19_rev":s19,"s20_profit":s20,"s21_gpr":s21,"s22_npr":s22,"s23_holders":s23,"s24_bamount":s24,"s25_bcount":s25,"s26_samount":s26,"s27_scount":s27,"s28_net":s28});

#循环开始

x = 1
for x in range(1,(nrows-1)):
 	

	data = xlrd.open_workbook('d:/py/test/today_stock.xlsx')
	table = data.sheets()[0] #:表示xls文件的第一个表格，[1]表示第二个表格
	#nrows = table.nrows 
	#print(nrows)
	s_code = table.cell(x,0).value #读取特定行特定列的内容


	print("================== ")
	#print(s_concept[s_concept["code"]==s_code])  #查找二维数组指定值
	#单行显示个股概念
	##定义 s_temp_concept 为该股票查询出来的概念分类--可能有多个
	s_temp_concept = s_concept[s_concept["code"]==s_code]  
	
	#print(s_temp_concept.ix[s_temp_concept["c_name"]])
	#s_temp_temp_concept = s_temp_concept.ix[s_temp_concept["c_name"]]
	#将多个概念分类汇总成一张新表，然后把概念列(c_name)传递给gainian，以便写入csv文件
	gainian = s_temp_concept['c_name'].values  
	#print(gainian)
	#s_temp_area = s_area[s_area["code"]==s_code]

	#print(s_temp_area[['area']])
	#s_t_t_area = s_temp_area[['area']]
	#s_t_t_name = s_temp_area[['name']]
	
	
	#s_temp_temp_area = s_t_t_area.iat[0,0]
	#s_name = s_t_t_name.iat[0,0]
	#print(s_temp_temp_area)
	#print(s_t_t_area.iat[0,0])

	#机构信息
	s_temp_inst = s_inst[s_inst["code"]==s_code]
	s_t_t_bamount = s_temp_inst[['bamount']]
	s_t_t_bcount = s_temp_inst[['bcount']]
	s_t_t_samount = s_temp_inst[['samount']]
	s_t_t_scount = s_temp_inst[['scount']]
	s_t_t_net = s_temp_inst[['net']]
	if s_temp_inst.empty == True:
		bamount = "0"
		bcount = "0"
		samount ="0"
		scount = "0"
		net = "0"
	
	else:

		bamount = s_t_t_bamount.iat[0,0]
		bcount = s_t_t_bcount.iat[0,0]
		samount =s_t_t_samount.iat[0,0]
		scount = s_t_t_scount.iat[0,0]
		net = s_t_t_net.iat[0,0]
	
	#s_temp_industry = s_industry[s_industry["code"]==s_code]
	#s_temp_temp_industry = s_temp_industry[['c_name']]
	#print(s_name,s_temp_temp_industry,s_temp_temp_area)

	#基本面信息，注意取数方式的不同，因为这张表其实没有code列，code是个排序列。
	s_temp_basics = s_basics.ix[s_code] 
	#print(s_temp_basics)
	s_name = s_temp_basics[u'name']
	s_temp_temp_area = s_temp_basics[u'area']
	s_temp_temp_industry = s_temp_basics[u'industry']
	pe = s_temp_basics[u'pe']
	outstanding = s_temp_basics[u'outstanding']
	totals = s_temp_basics[u'totals']
	totalAssets = s_temp_basics[u'totalAssets']
	liquidAssets = s_temp_basics[u'liquidAssets']
	fixedAssets = s_temp_basics[u'fixedAssets']
	reserved = s_temp_basics[u'reserved']
	reservedPerShare =s_temp_basics[u'reservedPerShare']
	esp = s_temp_basics[u'esp']
	bvps = s_temp_basics[u'bvps']
	pb = s_temp_basics[u'pb']
	timeToMarket = s_temp_basics[u'timeToMarket']
	undp = s_temp_basics[u'undp']
	perundp = s_temp_basics[u'perundp']
	rev = s_temp_basics[u'rev']
	profit = s_temp_basics[u'profit']
	gpr = s_temp_basics[u'gpr']
	npr = s_temp_basics[u'npr']
	holders = s_temp_basics[u'holders']
	#s_temp_basics = s_basics[s_basics["code"]==s_code]  
	#print(s_temp_basics)

	#print(" ")
	#print(s_industry[s_industry["code"]==s_code])  #查找二维数组指定值

	#print(" ")
	#print(s_area[s_area["code"]==s_code])  #查找二维数组指定值

	#获取五日均线、二十日均线、五日均量、二十日均量
	df = ts.get_hist_data(s_code,start=s_date,end=s_date) 
	#df = ts.get_hist_data(start=s_date,end=s_date)  
	if type(df) == 'NoneType':
		close = "0"
		ma5 ="0"
		ma10 ="0"
		ma20 ="0"
		vma5 = "0"
		vma10 = "0"
		vma20 = "0"
		volume = "0"
		turnover = "0"
		p_change = "0"

	else:

		close = df.ix[0,'close']
		ma5 =df.ix[0,'ma5']
		ma10 =df.ix[0,'ma10']
		ma20 =df.ix[0,'ma20']
		vma5 = df.ix[0,'v_ma5']
		vma10 = df.ix[0,'v_ma10']
		vma20 = df.ix[0,'v_ma20']
		volume = df.ix[0,'volume']
		turnover = df.ix[0,'turnover']
		p_change = df.ix[0,'p_change']
	print(s_code,s_name,s_temp_temp_industry,s_temp_temp_area)
	#将以上内容写入DataFrame表
	
	#print (s_stock)

	s_stock_all=s_stock.append(pd.Series([(s_code),s_name,s_date,close,ma5, ma10,ma20,vma5,vma10,vma20,volume,turnover,p_change,gainian,s_temp_temp_area,s_temp_temp_industry,pe,outstanding,rev,profit,gpr,npr,holders,bamount,bcount,samount,scount,net],index=['code','name','date','close','MA5','MA10','MA20','VMA5','VMA10','VMA20','volume','turnover','p_change','gainian','area','industry','pe','outstanding','rev','profit','gpr','npr','holders','bamount','bcount','samount','scount','net']),ignore_index=True)

	#print(s_stock_all)
	print("共计",(nrows-1),"支，当前是",x,"支... ...")
x = x + 1


s_stock_all.to_excel("d:/py/stock/s_stock_test_20170627.xlsx")